1 Imports System.Data.SqlClient
2 Imports System.IO
3 Imports Excel = Microsoft.Office.Interop.Excel
4 Public Class frmStaffRecord1
5 Public Sub GetData()
6 Try
7 con = New SqlConnection(cs)
8 con.Open()
9 cmd = New SqlCommand("Select RTRIM(St_ID) as [ID], RTRIM(StaffID) as [Staff ID], RTRIM(StaffName) as [Staff Name], Convert(DateTime,DateOfJoining,103) as [Joining Date], RTRIM(Gender) as [Gender], RTRIM(FatherName) as [Father's Name], RTRIM(TemporaryAddress) as [Temporary Address], RTRIM(PermanentAddress) as [Permanent Address], RTRIM(Designation) as [Designation], RTRIM(Qualifications) as [Qualifications], Convert(DateTime,DOB,103) as [DOB], RTRIM(PhoneNo) as [Phone No.], RTRIM(MobileNo) as [Mobile No.], RTRIM(Staff.Email) as [Email ID],RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(ClassType) as [Class Type],RTRIM(Salary) as [Basic Salary],RTRIM(AccountName) as [Account Name],RTRIM(AccountNumber) as [Account No.],RTRIM(Bank) as [Bank],RTRIM(Branch) as [Branch],RTRIM(IFSCcode) as [IFSC Code] ,RTRIM(Status) as [Status] from Staff,ClassType,SchoolInfo where Staff.ClassType=ClassType.Type and Staff.SchoolID=SchoolInfo.S_ID order by StaffName", con)
10 adp = New SqlDataAdapter(cmd)
11 ds = New DataSet()
12 adp.Fill(ds, "Staff")
13 dgw.DataSource = ds.Tables("Staff").DefaultView
14 con.Close()
15 Catch ex As Exception
16 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
17 End Try
18 End Sub
19
20 Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
21 Me.Close()
22 End Sub
23
24
25 Sub Reset()
26 txtStaffName.Text = ""
27 dtpDateFrom.Text = Today
28 dtpDateTo.Text = Today
29 GetData()
30 End Sub
31 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
32 Reset()
33 End Sub
34
35 Private Sub frmStudentRecord_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
36 GetData()
37 End Sub
38
39 Private Sub dgw_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
40 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
41 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
42 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
43 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
44 End If
45 Dim b As Brush = SystemBrushes.ControlText
46 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
47
48 End Sub
49
50 Private Sub txtStaffName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStaffName.TextChanged
51 Try
52 con = New SqlConnection(cs)
53 con.Open()
54 cmd = New SqlCommand("Select RTRIM(St_ID) as [ID], RTRIM(StaffID) as [Staff ID], RTRIM(StaffName) as [Staff Name], Convert(DateTime,DateOfJoining,103) as [Joining Date], RTRIM(Gender) as [Gender], RTRIM(FatherName) as [Father's Name], RTRIM(TemporaryAddress) as [Temporary Address], RTRIM(PermanentAddress) as [Permanent Address], RTRIM(Designation) as [Designation], RTRIM(Qualifications) as [Qualifications], Convert(DateTime,DOB,103) as [DOB], RTRIM(PhoneNo) as [Phone No.], RTRIM(MobileNo) as [Mobile No.], RTRIM(Staff.Email) as [Email ID],RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(ClassType) as [Class Type],RTRIM(Salary) as [Basic Salary],RTRIM(AccountName) as [Account Name],RTRIM(AccountNumber) as [Account No.],RTRIM(Bank) as [Bank],RTRIM(Branch) as [Branch],RTRIM(IFSCcode) as [IFSC Code] ,RTRIM(Status) as [Status] from Staff,ClassType,SchoolInfo where Staff.ClassType=ClassType.Type and Staff.SchoolID=SchoolInfo.S_ID where Staffname like '" & txtStaffName.Text & "%' order by StaffName", con)
55 adp = New SqlDataAdapter(cmd)
56 ds = New DataSet()
57 adp.Fill(ds, "Staff")
58 dgw.DataSource = ds.Tables("Staff").DefaultView
59 con.Close()
60 Catch ex As Exception
61 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
62 End Try
63 End Sub
64
65 Private Sub dtpDateTo_Validating(sender As System.Object, e As System.ComponentModel.CancelEventArgs) Handles dtpDateTo.Validating
66 If (dtpDateFrom.Value.Date) > (dtpDateTo.Value.Date) Then
67 MessageBox.Show("Invalid Selection", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
68 dtpDateTo.Focus()
69 End If
70 End Sub
71
72 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
73 Try
74 con = New SqlConnection(cs)
75 con.Open()
76 cmd = New SqlCommand("Select RTRIM(St_ID) as [ID], RTRIM(StaffID) as [Staff ID], RTRIM(StaffName) as [Staff Name], Convert(DateTime,DateOfJoining,103) as [Joining Date], RTRIM(Gender) as [Gender], RTRIM(FatherName) as [Father's Name], RTRIM(TemporaryAddress) as [Temporary Address], RTRIM(PermanentAddress) as [Permanent Address], RTRIM(Designation) as [Designation], RTRIM(Qualifications) as [Qualifications], Convert(DateTime,DOB,103) as [DOB], RTRIM(PhoneNo) as [Phone No.], RTRIM(MobileNo) as [Mobile No.], RTRIM(Staff.Email) as [Email ID],RTRIM(SchoolID) as [School ID],RTRIM(SchoolName) as [School Name],RTRIM(ClassType) as [Class Type],RTRIM(Salary) as [Basic Salary],RTRIM(AccountName) as [Account Name],RTRIM(AccountNumber) as [Account No.],RTRIM(Bank) as [Bank],RTRIM(Branch) as [Branch],RTRIM(IFSCcode) as [IFSC Code] ,RTRIM(Status) as [Status] from Staff,ClassType,SchoolInfo where Staff.ClassType=ClassType.Type and Staff.SchoolID=SchoolInfo.S_ID where DateOfJoining between @d1 and @d2 order by StaffName", con)
77 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
78 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
79 adp = New SqlDataAdapter(cmd)
80 ds = New DataSet()
81 adp.Fill(ds, "Staff")
82 dgw.DataSource = ds.Tables("Staff").DefaultView
83 con.Close()
84 Catch ex As Exception
85 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
86 End Try
87 End Sub
88
89 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
90 Dim rowsTotal, colsTotal As Short
91 Dim I, j, iC As Short
92 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
93 Dim xlApp As New Excel.Application
94 Try
95 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
96 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
97 xlApp.Visible = True
98
99 rowsTotal = dgw.RowCount
100 colsTotal = dgw.Columns.Count - 1
101 With excelWorksheet
102 .Cells.Select()
103 .Cells.Delete()
104 For iC = 0 To colsTotal
105 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
106 Next
107 For I = 0 To rowsTotal - 1
108 For j = 0 To colsTotal
109 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
110 Next j
111 Next I
112 .Rows("1:1").Font.FontStyle = "Bold"
113 .Rows("1:1").Font.Size = 12
114
115 .Cells.Columns.AutoFit()
116 .Cells.Select()
117 .Cells.EntireColumn.AutoFit()
118 .Cells(1, 1).Select()
119 End With
120 Catch ex As Exception
121 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
122 Finally
123 'RELEASE ALLOACTED RESOURCES
124 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
125 xlApp = Nothing
126 End Try
127 End Sub
128 End Class
